The process of connecting to an ODBC source is similar to that of connecting to a relational database. The ODBC source node allows you to copy tables from a given database in your ODBC environment to the Pyramid data flow. After data ingestion, you can perform any required data cleansing, preparation, or Machine Learning.
Pyramid also enables you to directly query databases in your ODBC environment via the ODBC Direct node. Direct querying for ODBC is configured via a different node due to the different setup required for direct querying of ODBC sources.
- Click here to learn more about direct querying for ODBC sources.
Configure the ODBC Direct Source
With the ODBC node selected, choose the ODBC server (red highlight below). If you don't see the required server in the list, click the refresh button (green arrow). Admin users can add servers here by clicking the Add Server button (purple arrow).
Once you've chosen the server, select the required database from the second drop-down list (blue highlight). The database list can be refreshed if the required database doesn't appear there (yellow arrow).
\
Table Selection
The tables in the given source will be loaded and appear in the Tables window (orange highlight above), where they can be selected or deselected and added to the data flow.
Select 'Add Tables' to assign an individual node to each table; this is important if you intend to perform any data cleansing or manipulation. If you don't need to perform any cleansing, select 'Add as Multi-Select'; this option uses the multi-select function to copy all tables to a single node. The resulting node can be connected to a target node only.
Another way to add tables to the data flow is via the Select functions, where you can connect Table or Tables nodes. You can then input the column(s) for each select operation. Another option is to use the SQL Query node to copy a data set from the source using an SQL expression.
Example. Add Tables from ODBC
In this example, the user connected to the ODBC sever called 'Test', and the database 'Demodata'. All tables in the database were copied to the data flow using the 'Add Tables function; this produced a Table node for each table, each of which was connected to the source node:
Example. Add Multi-Select Table from ODBC
In this example, the tables in the database were added to the data flow using the Add as Multi-Select function. This produced a single multi-select Tables node (representing all of the given tables), which was connected to the source node (green highlight below). A node was added to the Description window as a reminder to use the multi-select function (blue highlight).
